﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Collections;
using System.Data;

namespace BaseHelper.Data
{
    public class MsSqlHelper:IDisposable
    {
        public string Error;
        public int PageSize = 30;
        public string _connstr;
        public string _lastSql;
        private DBInfo _dbInfo;
        private SqlConnection _conn;
        private object _locker = new object();
        private bool _isKeepLive = false;


        public string GetLastSql
        {
            get { return _lastSql; }
        }

        public bool IsConntected
        {
            get
            {
                if (_dbInfo.KeepLive)
                {
                    return !(_conn==null ||_conn.State == System.Data.ConnectionState.Closed || _conn.State == System.Data.ConnectionState.Broken);
                }

                return false;
            }
        }

        public MsSqlHelper(DBInfo db, bool isKeepLive=false)
        {
            _isKeepLive = isKeepLive;
            _dbInfo = db;
            _connstr = "Max Pool Size=1024;Min Pool Size=0;Connect Timeout=10;Connection Lifetime=1024;Data Source=" + db.IP + ";Initial Catalog=" + db.DBName + ";User ID=" + db.User + ";Password=" + db.Pass;
        }

        ~MsSqlHelper()
        {
            this.Dispose();

            GC.SuppressFinalize(this);
        }

        public bool Open(DBInfo db) 
        {
            _dbInfo = db;
            Error = "";
            _connstr = "Max Pool Size=1024;Connect Timeout=10;Min Pool Size=0;Connection Lifetime=1024;Data Source=" + db.IP + ";Initial Catalog=" + db.DBName + ";User ID=" + db.User + ";Password=" + db.Pass;
            try
            {
                using (SqlConnection conn = new SqlConnection(_connstr))
                {
                    conn.Open();
                    return true;
                }
            }
            catch (Exception ex){
                Error = ex.Message;
                return false;
            }
        }

        private SqlConnection GetSqlConnection()
        {
            if (_dbInfo.KeepLive)
            {
                lock (_locker)
                {
                    if (_conn == null)
                    {
                        _conn = new SqlConnection(_connstr);
                    }

                    if (!this.IsConntected)
                    {
                        try
                        {
                            _conn.Open();
                            return _conn;
                        }
                        catch (Exception ex)
                        {
                            Error = "MsSqlHelper无法连接数据库," + ex.Message;
                            throw ex;
                        }

                    }
                    else
                        return _conn;
                }
            }
            else
            {
                SqlConnection conn= new SqlConnection(_connstr);
                try
                {
                    conn.Open();
                    return conn;
                }
                catch (Exception ex)
                {
                    Error = "MsSqlHelper无法连接数据库," + ex.Message;
                    throw ex;
                }
   
            }
        }

        public Hashtable[] ExecuteHashtable(string sql) 
        {
            Hashtable[] hts = null;
            _lastSql = sql;
            Error = "";
            

            try
            {
                SqlConnection conn = GetSqlConnection();

                using (SqlCommand cmd = new SqlCommand(sql, conn))
                {
                    if (_dbInfo.KeepLive)
                    {
                        lock (_locker)
                        {
                            SqlDataReader dr = cmd.ExecuteReader();
                            
                            if (dr.HasRows)
                                hts = DataReaderToHashtable(dr);
                           
                            if (!dr.IsClosed)
                                dr.Close();
                            
                            
                        }
                    }
                    else
                    {
                        SqlDataReader dr = cmd.ExecuteReader();
                        lock (dr)
                        {
                            if (dr.HasRows)
                                hts = DataReaderToHashtable(dr);

                            if (!dr.IsClosed)
                                dr.Close();

                            conn.Close();
                        }
                    }
                   

                }
                
            }
            catch (Exception ex)
            {
                Error = ex.Message;
                LogHelper.Err("BaseHelper.Data.MsSqlHelper.cs", "ExecuteHashtable() ：" + sql, ex);
            }
            return hts;
        }

        public bool IsDataReaderHasRow(string sql)
        {
            bool hasRow = false;

            _lastSql = sql;
            Error = "";
           
            try
            {
                SqlConnection conn = GetSqlConnection();
           
                using (SqlCommand cmd = new SqlCommand(sql, conn))
                {
                    if (_dbInfo.KeepLive)
                    {
                        lock (_locker)
                        {
                            SqlDataReader dr = cmd.ExecuteReader();
                            hasRow = dr.HasRows;
                            dr.Close();
                        }
                    }
                    else
                    {
                        SqlDataReader dr = cmd.ExecuteReader();
                        hasRow = dr.HasRows;
                        dr.Close();
                        conn.Close();
                    }
                    
  
                }
                
            }
            catch (Exception ex)
            {
                Error = ex.Message;
                LogHelper.Err("BaseHelper.Data.MsSqlHelper.cs", "ExecuteHashtable()", ex);
            }
            return hasRow;
        }

        public int NonQuery(string sql, SqlParameter[] sqlParms = null)
        {
            _lastSql = sql;
            Error = "";

            try
            {
                SqlConnection conn = GetSqlConnection();

                using (SqlCommand cmd = new SqlCommand(sql, conn))
                {
                    if (_dbInfo.KeepLive)
                    {
                        lock (_locker)
                        {
                            if (sqlParms != null)
                                cmd.Parameters.AddRange(sqlParms);
                            int ret=cmd.ExecuteNonQuery() > 0 ? 1 : 0;
                            return ret;
                        }
                    }
                    else
                    {
                        if (sqlParms != null)
                            cmd.Parameters.AddRange(sqlParms);
                        int ret = cmd.ExecuteNonQuery() > 0 ? 1 : 0;
                        conn.Close();
                        return ret;
                    }

                }
                
            }
            catch(Exception ex) 
            {
                Error = ex.Message;
                LogHelper.Err("RT.Helper.Data.MsSqlHelper.cs", "ExecuteHashtable()->" +  sql, ex);
            }
            return -1000;
        }

        public Hashtable[] DataReaderToHashtable(SqlDataReader dr)
        {
            List<Hashtable> lt = new List<Hashtable>();
            lock (dr)
            {
                while (dr.Read())
                {
                    Hashtable ht = new Hashtable();
                    for (int i = 0; i < dr.FieldCount; i++)
                    {
                        if (!ht.ContainsKey(dr.GetName(i)))
                        {
                            //ht.Add(dr.GetName(i), System.Web.HttpUtility.HtmlDecode(dr[i].ToString()));
                            ht.Add(dr.GetName(i), dr[i]);

                        }
                    }
                    lt.Add(ht);
                }
                Error = "";
            }
            return lt.ToArray();
        }

        public int Insert(string table, Hashtable[] hts)
        {
            _lastSql = "";
            Error = "";

            try
            {
                DataTable dt = new DataTable();
                foreach (string key in hts[0].Keys)
                {
                    DataColumn dc = new DataColumn();
                    dc.AllowDBNull = true;
                
                    if(hts[0][key] is byte[])
                         dc.DataType=typeof(byte[]); 
                    else
                         dc.DataType=typeof(string);

                    dc.ColumnName = key;
                    dt.Columns.Add(dc);
                }

                foreach (Hashtable ht in hts)
                {
                    DataRow dr = dt.NewRow();
                    foreach(string key in ht.Keys)
                    {
                        dr[key] = ht[key];
                    }
                    dt.Rows.Add(dr);
                }


                SqlConnection conn = GetSqlConnection();

                using (SqlBulkCopy bulkCopy = new SqlBulkCopy(_connstr, SqlBulkCopyOptions.UseInternalTransaction))
                {
                    bulkCopy.BatchSize = 10000;
                    bulkCopy.DestinationTableName = table;
              
               
                    foreach (string key in hts[0].Keys)
                    {
                        bulkCopy.ColumnMappings.Add(key, key);
                    }

                    lock (_locker)
                    {
                        bulkCopy.WriteToServer(dt);
                    }

                    if (!_dbInfo.KeepLive)
                    {
                        conn.Close();
                    }

                    return hts.Length;
                }

                

            }
            catch (Exception ex)
            {
                Error = ex.Message;
                LogHelper.Err("RT.Helper.Data.MsSqlHelper.cs", "ExecuteHashtable()->Insert.SqlBulkCopy()", ex);
            }
            return -1000;
        }


        public int Insert(string table, Hashtable ht, bool HasBinaryField = false)
        {
            string keys = "";
            string values = "";
            SqlParameter[] sqlParms = null;

            if (HasBinaryField)
            {
                List<SqlParameter> lt = new List<SqlParameter>();
                SqlParameter param;

                foreach (string key in ht.Keys)
                {
                    keys += "[" + key + "],";
                    values += "@" + key + ",";

                    if (ht[key] is string)
                        param = new SqlParameter("@" + key,System.Web.HttpUtility.HtmlEncode( ht[key].ToString()));
                    else
                        param = new SqlParameter("@" + key,ht[key]);

                    lt.Add(param);
                }
                sqlParms = lt.ToArray();
            }
            else
            {
                foreach (string key in ht.Keys)
                {
                    keys += "[" + key + "],";
                    //values += "'" + System.Web.HttpUtility.HtmlEncode(ht[key].ToString()) + "',";
                    values += "'" + System.Web.HttpUtility.HtmlEncode(ht[key].ToString()) + "',";   //12.6 准备将这里屏蔽掉，因为select的时候byte【】 会出问题

                }
            }


            if (keys.Length > 0)
            {
                keys = keys.Substring(0, keys.Length - 1);
                values = values.Substring(0, values.Length - 1);
            }

            string sql = string.Format("insert into {0}({1}) values({2})", table, keys, values);

            return NonQuery(sql, sqlParms);
        }

        public Hashtable[] Select(string table, string where = "", string field = "*", string orderby = "",int page=-1,int pageSize=30)
        {
            string sql;

            if (page==-1)
            {
                sql = string.Format("select {0} from {1} {2} {3}", field, table, where.Length > 0 ? "where " + where : "", orderby.Length > 0 ? "order by " + orderby : "");
            }
            else
            {
                if(page>0)
                    page--;
                sql = string.Format("select * from (select row_number() over (order by {3}) row,{0} from {1} {2}) as sqlin where row>{4} and row<={5}  ", field, table, where.Length > 0 ? "where " + where : "", orderby.Length > 0 ? orderby : "a.id", page * pageSize, (page + 1) * pageSize);
            }            

            return ExecuteHashtable(sql);
        }

        public bool IsSelectHasRow(string table, string where = "")
        {
            string sql = string.Format("select * from {0} {1}", table, where.Length>0?"where " + where:"");
            return IsDataReaderHasRow(sql);
        }

        public int Update(string table,Hashtable ht,string where, bool HasBinaryField = false,bool isValue=false)
        {
            string values = "";
            SqlParameter[] sqlParms = null;

            if (HasBinaryField)
            {
                List<SqlParameter> lt = new List<SqlParameter>();
                SqlParameter param;

                foreach (string key in ht.Keys)
                {
                    values += "[" + key + "]=@" + key + ",";

                    if (ht[key] is string)
                        param = new SqlParameter("@" + key, System.Web.HttpUtility.HtmlEncode(ht[key].ToString()));
                    else
                        param = new SqlParameter("@" + key, ht[key]);

                    lt.Add(param);
                }
                sqlParms = lt.ToArray();
            }
            else
            {

                foreach (string key in ht.Keys)
                {
                    if (isValue)
                        values += "[" + key + "] " + ht[key].ToString() + ",";
                    else
                        values += "[" + key + "]='" + System.Web.HttpUtility.HtmlEncode(ht[key].ToString()) + "',";
                }
            }

            if (values.Length > 0)
            {
                values = values.Substring(0, values.Length - 1);
            }
            
            string sql = string.Format("UPDATE {0} set {1} where {2}",  table,values,where);
            return NonQuery(sql,sqlParms);
        }
        
        public int Delete(string table, string where="")
        {
            string sql = string.Format("delete from {0} {1}", table, where.Length > 0 ? "where " + where : "");
            return NonQuery(sql);
        }

        public string[] GetTables()
        {
            List<string> lt = new List<string>();

            string sql = "SELECT Name FROM SysObjects Where XType='U'";
            Hashtable[] hts = this.ExecuteHashtable(sql);
            if (hts != null)
            {
                foreach (Hashtable ht in hts)
                {
                    lt.Add(ht["Name"].ToString());                    
                }
            }

            return lt.ToArray();
        }

        public string[] GetFields(string table)
        {
            List<string> lt = new List<string>();

            Hashtable[] hts = this.ExecuteHashtable("SELECT COLUMN_NAME Name FROM INFORMATION_SCHEMA.columns WHERE TABLE_NAME='" + table + "'");
            if (hts != null)
            {
                foreach (Hashtable ht in hts)
                {
                    lt.Add(ht["Name"].ToString());
                }
            }

            return lt.ToArray();
        }

        public int Truncate(string tableName)
        {
            string sql = "truncate table " + tableName;
            return NonQuery(sql);
        }

        public int Count(string tableName,string where)
        {
            Hashtable[] ht = Select(tableName, where, "count(*) as datarow");
            if (ht != null)
            {
                return Convert.ToInt32(ht[0]["datarow"]);
            }

            return -1;
        }



        public void Close()
        {
            this.Dispose();
        }

        public void Dispose()
        {
            try
            {
                if (_conn != null)
                    _conn.Close();
                _conn = null;
                GC.SuppressFinalize(this);
            }
            catch { }
        }
    }
}
